from PySide6.QtGui import *
from PySide6.QtWidgets import *
from PySide6.QtCore import *
from common_code import *
import re
import win32com.client as win32
import pythoncom
import xlwings as xw
import concurrent.futures
from concurrent.futures.thread import ThreadPoolExecutor
from concurrent.futures import as_completed

MAX_WORKERS = os.cpu_count() * 2  # 根据CPU核心数动态设置线程数

# 定义第一个页面
class Page_Excel(QWidget):
    def __init__(self):
        super().__init__()
        self.label = QLabel("Excel文件变更检查工具[单元格、批注、图形](文本整体检查,非字符级检查)")
        self.label.setAlignment(Qt.AlignCenter)  # 方法1：直接设置对齐方式
        self.before_sd_path = OpenFileWidget('变更前SD文件:    ')
        self.after_sd_path = OpenFileWidget('变更后SD文件:    ')
        self.before_pd_path = OpenFileWidget('变更前PD文件:    ')
        self.after_pd_path = OpenFileWidget('变更后PD文件:    ')
        layout_1 = QHBoxLayout()
        self.sd_sheet = LineTextWidget(title = 'SD sheet:    ', max_length = 20, display_width = 200, text = "最大长度20个字符")
        self.pd_sheet = LineTextWidget(title = 'PD sheet:    ', max_length = 20, display_width = 200, text = "最大长度20个字符")
        layout_1.addWidget(self.sd_sheet)
        layout_1.addWidget(self.pd_sheet)
        self.start_btn = StartButtonWidget(self.start_exce, "Processing progress")
        self.table_window = TableWindow(self)
        layout = QVBoxLayout()
        layout.addWidget(self.label)
        layout.addWidget(self.before_sd_path)
        layout.addWidget(self.after_sd_path)
        layout.addWidget(self.before_pd_path)
        layout.addWidget(self.after_pd_path)
        layout.addLayout(layout_1)
        layout.addWidget(self.start_btn)
        self.label_1 = QLabel("注意 : 运行工具时，Excel 进程会被强制终止，请提前保存并关闭本地所有 Excel 文件，以免数据丢失。")
        self.label_1.setStyleSheet("color: red;")
        layout.addWidget(self.label_1)
        # 添加分隔线
        separator = QFrame()
        separator.setFrameShape(QFrame.HLine)
        separator.setFrameShadow(QFrame.Sunken)
        layout.addWidget(separator)
        layout.addWidget(self.table_window)
        self.setLayout(layout)

    def clear(self):
        # 清除UI中的输入和显示
        self.before_sd_path.clear()
        self.after_sd_path.clear()
        self.before_pd_path.clear()
        self.after_pd_path.clear()
        self.sd_sheet.clear()
        self.pd_sheet.clear()
        self.start_btn.clear()
        self.table_window.clear_table()
    
    def start_exce(self):
        self.table_window.clear_table()
        self.start_btn.ProgressBar(0)
        if not confirm_file_status(self):
            return
        
        sd_check_flag = False
        pd_check_flag = False
        before_sd_path = self.before_sd_path.edit.text().strip()
        after_sd_path = self.after_sd_path.edit.text().strip()
        if before_sd_path and after_sd_path:
            sd_check_flag = True

        before_pd_path = self.before_pd_path.edit.text().strip()
        after_pd_path = self.after_pd_path.edit.text().strip()
        if before_pd_path and after_pd_path:
            pd_check_flag = True

        if sd_check_flag is False and pd_check_flag is False:
            QMessageBox.critical(self, "错误", f"请选择要检查的文件 !!!")
            return

        sd_sheet = self.sd_sheet.line_text.text().strip()
        pd_sheet = self.pd_sheet.line_text.text().strip()
        if sd_check_flag and pd_check_flag:
            if not sd_sheet:
                QMessageBox.critical(self, "错误", f"[SD sheet]为空, 请输入")
                return
            if not pd_sheet:
                QMessageBox.critical(self, "错误", f"[PD sheet]为空, 请输入")
                return

        error_info_dict = {
                            "Value" : "文本不一致" ,
                            "fontName" : "字体不一致" ,
                            "fontSize" : "字体大小不一致" ,
                            "fontColor" : "字体颜色不一致" ,
                            "fontStrikethrough" : "删除线状态不一致" ,
                            "InteriorColor" : "填充色不一致"
                            }
        sample_data = []
        before_sd_dict = {}
        after_sd_dict = {}
        before_pd_dict = {}
        after_pd_dict = {}
        after_sd_id_dict = {}
        after_pd_id_dict = {}
        self.start_btn.ProgressBar(10)
        try:
            with ThreadPoolExecutor(max_workers=int(MAX_WORKERS / 3)) as executor:
                futures = {}
                if sd_check_flag:
                    futures[executor.submit(self.sd_pd_file_read, before_sd_path)] = 'before_sd'
                    futures[executor.submit(self.sd_pd_file_read, after_sd_path)] = 'after_sd'
                if pd_check_flag:
                    futures[executor.submit(self.sd_pd_file_read, before_pd_path)] = 'before_pd'
                    futures[executor.submit(self.sd_pd_file_read, after_pd_path)] = 'after_pd'
                results = {}
                completed_count = 0
                total_count = len(futures)
                # 按完成顺序处理
                for future in as_completed(futures):
                    name = futures[future]
                    try:
                        results[name] = future.result()
                        completed_count += 1
                        progress = 10 + (completed_count / total_count * 30)  # 10%-40%
                        self.start_btn.ProgressBar(int(progress))
                        print(f"{name} 读取完成")
                    except Exception as e:
                        print(f"{name} 读取失败: {e}")
                        results[name] = {}

                # 按需使用结果
                if sd_check_flag:
                    before_sd_dict = results.get('before_sd', {})
                    after_sd_dict = results.get('after_sd', {})
                
                if pd_check_flag:
                    before_pd_dict = results.get('before_pd', {})
                    after_pd_dict = results.get('after_pd', {})

            self.start_btn.ProgressBar(50)
            for sheet,data in before_sd_dict.items():
                if sheet not in after_sd_dict:
                    sample_data.append([after_sd_path, sheet, "-", "删除sheet页", "-", "-"])
            for sheet,data in after_sd_dict.items():
                if sheet in before_sd_dict:
                    before_data = before_sd_dict[sheet]["data"]
                    after_data = data["data"]
                    for cell,value in after_data.items():
                        if cell in before_data:
                            b_d = before_data[cell]
                            for k,v in value.items():
                                if v != b_d[k]:
                                    sample_data.append([after_sd_path, sheet, cell, f"单元格{error_info_dict[k]}", b_d[k], v])
                        else:
                            sample_data.append([after_sd_path, sheet, cell, "新增单元格", "-", value["Value"]])
                            
                        if sheet == sd_sheet:
                            if cell.startswith("B") and value["Value"].startswith("BEVIVIARL"):
                                after_sd_id_dict[cell] = value["Value"]

                    for cell,value in before_data.items():
                        if cell not in after_data:
                            sample_data.append([after_sd_path, sheet, cell, "删除单元格", value["Value"], "-"])
                    
                    before_comment = before_sd_dict[sheet]["comment"]
                    after_comment = data["comment"]
                    for cell,value in after_comment.items():
                        if cell in before_comment:
                            b_d = before_comment[cell]
                            for k,v in value.items():
                                if v != b_d[k]:
                                    sample_data.append([after_sd_path, sheet, cell, f"批注{error_info_dict[k]}", b_d[k], v])
                        else:
                            sample_data.append([after_sd_path, sheet, cell, "新增批注", "-", value["Value"]])
                    for cell,value in before_comment.items():
                        if cell not in after_comment:
                            sample_data.append([after_sd_path, sheet, cell, "删除批注", value["Value"], "-"])

                    before_shape = before_sd_dict[sheet]["shape"]
                    after_shape = data["shape"]
                    for shape,value in after_shape.items():
                        if shape in before_shape:
                            b_s = before_shape[shape]
                            for k,v in value.items():
                                if k != "address" and v != b_s[k]:
                                    sample_data.append([after_sd_path, sheet, value["address"], f"图形{error_info_dict[k]}", b_s[k], v])
                        else:
                            sample_data.append([after_sd_path, sheet, value["address"], "新增图形", "-", f"[{shape}][{value["Value"]}]"])
                    for shape,value in before_shape.items():
                        if shape not in after_shape:
                            sample_data.append([before_sd_path, sheet, value["address"], "删除图形", f"[{shape}][{value["Value"]}]", "-"])

                else:
                    sample_data.append([after_sd_path, sheet, "-", "新增sheet页", "-", "-"])
                
            self.start_btn.ProgressBar(70)
            for sheet,data in before_pd_dict.items():
                if sheet not in after_pd_dict:
                    sample_data.append([after_pd_path, sheet, "-", "删除sheet页", "-", "-"])
            for sheet,data in after_pd_dict.items():
                if sheet in before_pd_dict:
                    before_data = before_pd_dict[sheet]["data"]
                    after_data = data["data"]
                    for cell,value in after_data.items():
                        if cell in before_data:
                            b_d = before_data[cell]
                            for k,v in value.items():
                                if v != b_d[k]:
                                    sample_data.append([after_pd_path, sheet, cell, f"单元格{error_info_dict[k]}", b_d[k], v])
                        else:
                            sample_data.append([after_pd_path, sheet, cell, "新增单元格", "-", value["Value"]])
                            
                        if sheet == pd_sheet:
                            if cell.startswith("B") and value["Value"].startswith("BEVIVIARL"):
                                after_pd_id_dict[cell] = value["Value"]
                    for cell,value in before_data.items():
                        if cell not in after_data:
                            sample_data.append([after_pd_path, sheet, cell, "删除单元格", value["Value"], "-"])
                    
                    before_comment = before_pd_dict[sheet]["comment"]
                    after_comment = data["comment"]
                    for cell,value in after_comment.items():
                        if cell in before_comment:
                            b_d = before_comment[cell]
                            for k,v in value.items():
                                if v != b_d[k]:
                                    sample_data.append([after_pd_path, sheet, cell, f"批注{error_info_dict[k]}", b_d[k], v])
                        else:
                            sample_data.append([after_pd_path, sheet, cell, "新增批注", "-", value["Value"]])
                    for cell,value in before_comment.items():
                        if cell not in after_comment:
                            sample_data.append([after_pd_path, sheet, cell, "删除批注", value["Value"], "-"])

                    before_shape = before_pd_dict[sheet]["shape"]
                    after_shape = data["shape"]
                    for shape,value in after_shape.items():
                        if shape in before_shape:
                            b_s = before_shape[shape]
                            for k,v in value.items():
                                if k != "address" and v != b_s[k]:
                                    sample_data.append([after_pd_path, sheet, value["address"], f"图形{error_info_dict[k]}", b_s[k], v])
                        else:
                            sample_data.append([after_pd_path, sheet, value["address"], "新增图形", "-", f"[{shape}][{value["Value"]}]"])
                    for shape,value in before_shape.items():
                        if shape not in after_shape:
                            sample_data.append([before_pd_path, sheet, value["address"], "删除图形", f"[{shape}][{value["Value"]}]", "-"])
                else:
                    sample_data.append([after_pd_path, sheet, "-", "-", "-", "新增sheet页"])

            self.start_btn.ProgressBar(90)
            if sd_check_flag and pd_check_flag:
                for cell,value in after_sd_id_dict.items():
                    id_flag = False
                    for _,pvalue in after_pd_id_dict.items():
                        if value == pvalue:
                            id_flag = True
                            break
                    if id_flag == False:
                        sample_data.append([after_sd_path, sd_sheet, cell, "新增要件番号数据", "-", "-"])
                for cell,value in after_pd_id_dict.items():
                    id_flag = False
                    for _,pvalue in after_sd_id_dict.items():
                        if value == pvalue:
                            id_flag = True
                            break
                    if id_flag == False:
                        sample_data.append([after_pd_path, pd_sheet, cell, "错误要件番号数据", "-", "-"])
        except Exception as e:
            print(e)
            QMessageBox.information(self, "错误", f"文件检查失败:[{e}]")
            return
            
        self.start_btn.ProgressBar(100)
        if sample_data:
            self.table_window.set_data(sample_data)
            self.table_window.set_header(["file name" , "sheet" , "Address" , "error Text", "before value", "after value"])
            self.table_window.show_table()
            QMessageBox.information(self, "完成", "文件检查已完成，请查看操作日志 !!!")
        else:
            QMessageBox.information(self, "完成", "文件检查已完成 !!!")
    
    def sd_pd_file_read(self,file_path):
        file_dict = {}
        with ExcelHandler() as excel:
            if excel.open_workbook(file_path):
                all_sheet = excel.get_all_sheet_names()
                sheets_count = len(all_sheet)
                for sheet_name in all_sheet:
                    data_dict = {}
                    comment_dict = {}
                    shape_dict = {}
                    sheet = excel.get_sheet(sheet_name)
                    sheet.Activate()  # 确保目标 Sheet 是活动状态
                    # time.sleep(0.5)
                    # # 强制Excel应用程序重绘屏幕
                    # excel.excel_app.ScreenUpdating = False  # 先关闭屏幕更新
                    # excel.excel_app.ScreenUpdating = True   # 再开启屏幕更新，这会触发重绘
                    # excel.excel_app.ActiveWindow.Zoom = 100  # 设置当前活动窗口的缩放比例,强制100%显示
                    # sheet.Tab.Color = False  # False/-1 标签变为透明/无色
                    last_row = sheet.Cells.Find("*", SearchOrder=win32.constants.xlByRows, SearchDirection=win32.constants.xlPrevious).Row
                    last_col = sheet.Cells.Find("*", SearchOrder=win32.constants.xlByColumns, SearchDirection=win32.constants.xlPrevious).Column
                    print(f"file_path={file_path}")
                    print(f"sheet_name = {sheet_name} last_row={last_row} last_col={last_col}")
                    range_cell = sheet.Range(f"A1:{get_column_letter(last_col)}{last_row}")
                    for cell in range_cell:
                        font = cell.Font
                        Value = str(cell.Value).strip()
                        address = str(cell.Address).replace("$","")
                        if Value != "None" and Value != "":
                            data_dict[address] = {
                                "Value" : str(cell.Value).strip() ,
                                "fontName" : font.Name ,
                                "fontSize" : font.Size ,
                                "fontColor" : font.Color ,
                                "fontStrikethrough" : font.Strikethrough ,
                                "InteriorColor" : cell.Interior.Color
                                }
                        try:
                            if cell.Comment:
                                comment = cell.Comment
                                cfont = comment.Shape.TextFrame.Characters().Font
                                comment_dict[address] = {
                                    "Value" : str(comment.Text()).strip() ,
                                    "fontName" : cfont.Name ,
                                    "fontSize" : cfont.Size ,
                                    "fontStrikethrough" : cfont.Strikethrough
                                }
                        except Exception as e:
                            print(f"{address} 批注信息读取失败: {e}")

                    # 遍历所有形状（包括文本框和矩形）
                    for shape in sheet.Shapes:
                        top_left_cell = shape.TopLeftCell
                        bottom_right_cell = shape.BottomRightCell
                        # 检查行是否隐藏
                        rows_hidden = any(sheet.Rows(row).Hidden 
                                    for row in range(top_left_cell.Row, bottom_right_cell.Row + 1))
                        # 检查列是否隐藏
                        cols_hidden = any(sheet.Columns(col).Hidden 
                                    for col in range(top_left_cell.Column, bottom_right_cell.Column + 1))
                        if not rows_hidden and not cols_hidden and shape.Visible == -1:
                            if shape.Type == 6:  # 6表示组合图形(msoGroup)
                                # print(f"发现组合图形: {shape.Name}")
                                get_group_shape(shape, shape_dict)
                            else:
                                # 处理普通图形
                                get_single_shape(shape, shape_dict)
                    file_dict[sheet_name] = { "data" : data_dict.copy(), "comment" : comment_dict.copy(), "shape" : shape_dict.copy() }
        return file_dict


# with ThreadPoolExecutor(max_workers=int(MAX_WORKERS / 3)) as executor:
#     # 并行读取两个文件的所有工作表
#     futures_1 = {executor.submit(self._read_sheet_data, file_path_1, sheet): sheet 
#                 for sheet in common_sheets}
#     futures_2 = {executor.submit(self._read_sheet_data, file_path_2, sheet): sheet 
#                 for sheet in common_sheets}
#     # 等待所有读取任务完成
#     file_data_1, file_data_2 = {}, {}
#     for future in as_completed(futures_1):
#         sheet = futures_1[future]
#         file_data_1[sheet] = future.result()
    
#     for future in as_completed(futures_2):
#         sheet = futures_2[future]
#         file_data_2[sheet] = future.result()

#     # 并行比较所有工作表
#     diff_futures = []
#     for sheet in common_sheets:
#         future = executor.submit(
#             self.diff_worksheet_5_openpyxl,
#             file_data_1[sheet],
#             file_data_2[sheet],
#             sheet,
#             workbook_tab_color_map_2
#         )
#         diff_futures.append(future)
        
#     for future in as_completed(diff_futures):
#         sheet_diffs = future.result()  # 在这里获取结果
#         if sheet_diffs:
#             sorted_data = sorted(sheet_diffs, key=lambda x: (x[1], self.cell_sort_key(x[2])))
#             diff_result_list.extend(sorted_data)